#!/usr/bin/env python
#coding: utf-8

import xlwt
import datetime
from math import ceil
from pycalendar import *
from data import *
from pyecharts.charts import Graph
from pyecharts import options as opts


class Query(QWidget):

    def __init__(self):
        super(Query, self).__init__()
        self.regster = ''
        self.setWindowTitle("查询")
        self.tmp = 0
        self.count = 0
        self.present_page = 1
        self.max_page = 1
        self.win = 0
        self.resize(960,800)
        self.db = DB()
        self.cur = self.db.cur
        self.center()
        self.set_ui()

    def set_ui(self):
        self.name_label = QLabel("姓    名")
        self.name = QLineEdit()
        self.name.setPlaceholderText("输入姓名或身份证")
        self.name.setClearButtonEnabled(True)
        self.begin_date_label = QLabel("起始日期")
        self.begin_date = QDateEdit()
        self.begin_date.setDate(QDate.currentDate().addMonths(-1))
        self.begin_date_bnt = QPushButton()
        self.begin_date_bnt.setStyleSheet("border:hidden;")
        self.begin_date_bnt.setIcon(QIcon('./img/cal.ico'))
        self.begin_date_bnt.clicked.connect(lambda:self.show_cal(self.begin_date))
        self.end_date_label = QLabel("截止日期")
        self.end_date = QDateEdit()
        self.end_date.setDate(QDate.currentDate().addDays(1))
        self.end_date_bnt = QPushButton()
        self.end_date_bnt.setStyleSheet("border:hidden;")
        self.end_date_bnt.setIcon(QIcon('./img/cal.ico'))
        self.end_date_bnt.clicked.connect(lambda:self.show_cal(self.end_date))

        self.status1 = QCheckBox("健康人群")
        self.status2 = QCheckBox("一般接触者")
        self.status3 = QCheckBox("密接")
        self.status4 = QCheckBox("无症状感染者")
        self.status5 = QCheckBox("确诊病例")
        self.status6 = QCheckBox("康复")
        self.status7 = QCheckBox("全部")
        self.status8 = QCheckBox("初筛阳性")
        self.status9 = QCheckBox("疑似病例")
        self.status7.setChecked(True)

        self.radio1= QRadioButton("全部画图")
        self.radio2= QRadioButton("感染画图")
        self.radio2.setChecked(True)

        self.loc_checkbox= QCheckBox("包含地址")
        self.loc_checkbox.setChecked(True)

        status_widget  = QGroupBox()
        status_layout = QHBoxLayout()
        for i in [self.status1,self.status2,self.status3,self.status8,self.status9,self.status4,
                    self.status5,self.status6,self.status7]:
            status_layout.addWidget(i)
        status_widget.setLayout(status_layout)

        radio_widget = QGroupBox()
        radio_layout = QHBoxLayout()
        radio_layout.addWidget(self.radio1)
        radio_layout.addWidget(self.radio2)
        radio_widget.setLayout(radio_layout)

        self.query_bnt = QPushButton("查询")
        self.query_bnt.clicked.connect(lambda:self.query_record())
        self.query_bnt.setStyleSheet("background-color:green;border:hidden;color:white;font-size:20px;text-align:center;width:60px;height:60px;")
        self.graph_bnt = QPushButton("关系图")
        self.graph_bnt.clicked.connect(self.gen_graph)
        self.graph_bnt.setStyleSheet("background-color:blue;border:hidden;color:white;font-size:20px;text-align:center;width:60px;height:60px;")

        self.export_person_bnt = QPushButton("导出病例")
        self.export_mijie_bnt = QPushButton("导出密接")
        self.export_cimi_bnt = QPushButton("导出次密接")
        self.export_mijie_bnt.clicked.connect(lambda:self.export_mijie(1))
        self.export_cimi_bnt.clicked.connect(lambda:self.export_mijie(0))
        self.export_person_bnt.clicked.connect(self.export_patient)
        self.export_mijie_bnt.setStyleSheet("background-color:green;color:white;border:none;height:26px;")
        self.export_cimi_bnt.setStyleSheet("background-color:green;color:white;border:none;height:26px;")
        self.export_person_bnt.setStyleSheet("background-color:green;color:white;border:none;height:26px;")

        self.pre_page = QPushButton("前一页")
        self.next_page = QPushButton("后一页")
        self.pre_page.setStyleSheet("background-color:green;color:white;border:none;height:26px;")
        self.next_page.setStyleSheet("background-color:green;color:white;border:none;height:26px;")
        self.page_info = QLabel()
        self.page_info = QLabel(' 共？条，共？页，第？页 ')
        self.page_info.setAlignment(Qt.AlignCenter)
        self.page_info.setStyleSheet("color:green;border:1px solid  green;width:100px;height:24px;")
        self.pre_page.clicked.connect(self.to_pre_page)
        self.next_page.clicked.connect(self.to_next_page)

        self.table = QTableWidget(15,10)
        table_width_list = [200,80,150,50,50,100,150,80,80,80]
        for i in range(10):
            self.table.setColumnWidth(i,table_width_list[i])

        self.head_layout = QGridLayout()
        self.head_layout.addWidget(self.name_label,0,0)
        self.head_layout.addWidget(self.name,0,1,1,2)
        self.head_layout.addWidget(self.begin_date_label,0,3)
        self.head_layout.addWidget(self.begin_date,0,4)
        self.head_layout.addWidget(self.begin_date_bnt,0,5)
        self.head_layout.addWidget(self.end_date_label,0,6)
        self.head_layout.addWidget(self.end_date,0,7)
        self.head_layout.addWidget(self.end_date_bnt,0,8)

        self.head_layout_main = QWidget()
        self.head_layout_main.setLayout(self.head_layout)

        self.bnt_layout = QHBoxLayout()
        self.bnt_layout.addWidget(self.query_bnt)
        self.bnt_layout.addWidget(self.graph_bnt)
        self.bnt_layout2 = QWidget()
        self.bnt_layout2.setLayout(self.bnt_layout)

        self.export_layout = QHBoxLayout()
        self.export_widget = QWidget()
        self.export_layout.addWidget(self.export_mijie_bnt)
        self.export_layout.addWidget(self.export_cimi_bnt)
        self.export_layout.addWidget(self.export_person_bnt)
        self.export_widget.setLayout(self.export_layout)

        self.page_layout = QHBoxLayout()
        self.page_layout.addWidget(self.pre_page)
        self.page_layout.addWidget(self.page_info)
        self.page_layout.addWidget(self.next_page)
        self.page_layout2 = QWidget()
        self.page_layout2.setLayout(self.page_layout)


        self.main_layout = QGridLayout()
        self.main_layout.addWidget(self.head_layout_main,0,0,1,7)
        self.main_layout.addWidget(status_widget,1,0,1,4)
        self.main_layout.addWidget(self.loc_checkbox,1,4,1,1)
        self.main_layout.addWidget(radio_widget,1,5,1,2)
        self.main_layout.addWidget(self.bnt_layout2,0,7,2,2)
        self.main_layout.addWidget(self.table,3,0,20,9)
        self.main_layout.addWidget(self.export_widget,24,0,1,3)
        self.main_layout.addWidget(self.page_layout2,24,5,1,4)

        self.setLayout(self.main_layout)

    def query_record(self,start = 0, numbers = 15):

        self.db2 = DB()
        self.cur2 = self.db2.cur

        begin_date_interge = self.change_date(self.begin_date)
        end_date_interge = self.change_date(self.end_date)

        status_list = ["凑数",'凑数2']
        if self.status1.isChecked():
            status_list.append('健康人群')
        if self.status2.isChecked():
            status_list.append("一般接触者")
        if self.status3.isChecked():
            status_list.append("密接")
        if self.status8.isChecked():
            status_list.append("初筛阳性")
        if self.status9.isChecked():
            status_list.append("疑似病例")
        if self.status4.isChecked():
            status_list.append("无症状感染者")
        if self.status5.isChecked():
            status_list.append("确诊病例")
        if self.status6.isChecked():
            status_list.append("康复")
        if self.status7.isChecked():
            sql = '''
                select name,id,gender,age,tel,address,present_status,
                present_measure,regist_date,uuid,is_deleted
                from person where regist_date between {} and {}
                '''.format(begin_date_interge,end_date_interge)
            number_sql = '''select count(uuid) from person where regist_date
                between {} and {}'''.format(begin_date_interge,end_date_interge)
        else:
            sql = '''
                select name,id,gender,age,tel,address,present_status,
                present_measure,regist_date,uuid,is_deleted
                from person where present_status in {} and regist_date between {} and {}
            '''.format(tuple(status_list),begin_date_interge,end_date_interge)
            number_sql = '''select count(uuid) from person
                where present_status in {}
                and regist_date between {} and {}
            '''.format(tuple(status_list),begin_date_interge,end_date_interge)

        if self.name.text() == '':
            name_sql = '  '
        else:
            name_text_tmp = self.name.text()
            name_text = '%' + name_text_tmp + '%'
            name_sql = ''' and  (person.name like "%s" or
                        person.id like "%s") '''%(name_text,name_text)

        if not start:
            sql3 = number_sql + name_sql
            self.cur2.execute(sql3)
            count1 = self.cur2.fetchone()
            self.count = count1[0]
            self.max_page = ceil(self.count/numbers)
            self.present_page = 1

        if numbers == 100:
            self.start = 0
            self.number = self.count
        elif numbers == 200:
            self.start = 0
            self.number = self.count
        else:
            self.start = start
            self.number = numbers

        sql2 = sql + name_sql + '  limit {} offset {} '.format(self.number,self.start)
        self.cur2.execute(sql2)
        rslt_all =  self.cur2.fetchall()
        self.db2.con.close()
        self.tmp = rslt_all

        pages_text = '共' + str(self.count) +'条, 共 ' + str(self.max_page) + '页，第' + str(self.present_page) +'页'
        self.page_info.setText(pages_text)
        self.refresh_table()

    def refresh_table(self):
        self.table.clear()
        self.table.setHorizontalHeaderLabels(['操作','姓名','证件号码','性别','年龄','联系方式','住址','现状','管理措施','登记日期'])
        k = 0  # k 行数
        # color_list = ["#000000","#4682b4","#ffd700","#DC1400","#DC143C","#FF0000","#008800","#4b0082"]
        color_list = ["#00FF00","#4682b4","#996633","#DC143c",'#DC143C',"#DC1400","#FF0000","#008800","#4b0082"]
        status_list = ['健康人群','一般接触者','密接','初筛阳性','疑似病例','无症状感染者','确诊病例','康复','死亡']
        for i in self.tmp:
            if k < 15:
                for j in range(9): #15columns 第一列为操作列
                    if j == 8:
                        try:
                            item_tmp = QTableWidgetItem(self.to_date(i[j]))
                        except:
                            item_tmp = QTableWidgetItem('')
                    else:
                        item_tmp = QTableWidgetItem(str(i[j]))
                    item_tmp.setTextAlignment(Qt.AlignCenter)
                    self.table.setItem(k,j+1,item_tmp)

                    try:
                        color = color_list[status_list.index(i[6])]
                    except:
                        color = Qt.black
                    # for m in range(1,10):
                    self.table.item(k,j+1).setForeground(QColor(color))

                self.table.setCellWidget(k,0,self.button_row(i[-2],i[-1]))
            else:
                break
            k += 1

    def to_next_page(self):
        self.present_page += 1
        if self.present_page < self.max_page:
            a =  (self.present_page-1) *15
            # b = a + 20
        else:
            # b = None
            a = (self.max_page-1)*15
            self.present_page = self.max_page
        self.query_record(start=a)
        # self.draw_table(self.tmp[a:b])

    def to_pre_page(self):
        self.present_page -= 1
        if self.present_page < 1:
            self.present_page = 1
        a = (self.present_page-1)*15
        # b = a + 20
        self.query_record(start=a)
        # self.draw_table(self.tmp[a:b])

    def show_cal(self,tmp):
        self.cal = Calendar(tmp)
        self.cal.show()
        self.cal.date_signal.connect(self.input_date)

    def input_date(self, list):
        list[0].setDate(list[1])

    def change_date(self,pyqtdate):   # a pyqtdate style /yyyymmdd to time stamp
        pydate = pyqtdate.date().toPyDate()
        base_date = datetime.date(1970,1,1)
        day_delta = pydate - base_date
        days = day_delta.days
        seconds = days*24*3600
        return seconds

    def to_date(self,a):
        b = datetime.datetime(1970, 1, 1) + datetime.timedelta(seconds=a)
        c = b.strftime('%Y/%m/%d')
        return c

    def button_row(self, uuid,mark):
        # self.db = DataBase()
        self.widget = QWidget()
        self.view_bnt = QPushButton('查看')
        self.travel_bnt = QPushButton('旅行史')
        if mark:
            self.del_bnt = QPushButton('恢复')
            self.del_bnt.clicked.connect(lambda:self.regret_record(uuid))
        else:
            self.del_bnt = QPushButton('删除')
            self.del_bnt.clicked.connect(lambda:self.del_record(uuid))
        self.interview_bnt = QPushButton("流调")
        self.measure_bnt = QPushButton("处理")

        self.view_bnt.setStyleSheet('''text-align:center; background-color:green;border-style:outset;height:20px;color:white;''')
        self.travel_bnt.setStyleSheet('''text-align:center;background-color:blue;border-style:outset;height:20px;color:white;''')
        self.interview_bnt.setStyleSheet('''text-align:center;background-color:brown;border-style:outset;height:20px;color:white;''')
        self.measure_bnt.setStyleSheet('''text-align:center;background-color:pink;border-style:outset;height:20px;color:white;''')
        self.del_bnt.setStyleSheet('''text-align:center;background-color:red;border-style:outset;height:20px;color:white;''')

        self.hlayout = QHBoxLayout()

        self.hlayout.addWidget(self.view_bnt)
        self.hlayout.addWidget(self.travel_bnt)
        self.hlayout.addWidget(self.measure_bnt)
        self.hlayout.addWidget(self.interview_bnt)
        self.hlayout.addWidget(self.del_bnt)

        self.view_bnt.clicked.connect(lambda:self.view_record(uuid))
        self.travel_bnt.clicked.connect(lambda:self.view_travel(uuid))
        # self.travel_bnt.clicked.connect(lambda:self.view_location(uuid))
        self.interview_bnt.clicked.connect(lambda:self.interview_record(uuid))
        self.measure_bnt.clicked.connect(lambda:self.view_measure(uuid))
        self.hlayout.setContentsMargins(5,2,5,2)
        self.widget.setLayout(self.hlayout)
        return self.widget

    def view_location(self,uuid):
        from  travel import Location_History
        if self.win:
            self.win.close()
        self.win = Location_History(uuid)
        self.win.show()
        self.win.close_bnt.clicked.connect(self.refresh)


    def view_travel(self,uuid):
        from  travel import Travel_History
        if self.win:
            self.win.close()
        self.win = Travel_History(uuid)
        self.win.show()
        self.win.close_bnt.clicked.connect(self.refresh)

    def view_measure(self,uuid):
        from measure import Measure_History
        if self.win:
            self.win.close()
        self.win =  Measure_History(uuid)
        self.win.show()
        self.win.refresh_table()
        self.win.close_bnt.clicked.connect(self.refresh)

    def view_record(self,uuid):
        # self.db = DB()
        # self.cur = self.db.cur
        from regist import Regist
        sql = '''select * from person where person.uuid = "{}"
        '''.format(uuid)
        self.cur.execute(sql)
        rslt = self.cur.fetchone()
        # self.db.con.close()
        if self.win:
            self.win.close()
        self.win = Regist()
        self.win.show()
        self.win.uuid.setText(uuid)
        self.win.name.setText(rslt[2])
        self.win.id.setText(rslt[3])
        self.win.gender.setCurrentText(rslt[4])
        self.win.age.setText(str(rslt[5]))
        self.win.tel.setText(rslt[6])
        self.win.address.setText(rslt[7])
        self.win.occup.setCurrentText(rslt[8])
        self.win.company.setText(rslt[9])
        self.win.regist_date.setDateTime(self.to_pydatetime(rslt[10]))
        self.win.measure.setCurrentText(rslt[12])
        self.win.status.setCurrentText(rslt[11])
        self.win.backup.setText(rslt[13])
        self.win.serial.setText(rslt[0])
        self.win.save_bnt.setText("更新")

    def interview_record(self,uuid):
        from contact import Contact_List
        if self.win:
            self.win.close()
        self.win =  Contact_List(uuid)
        self.win.show()
        self.win.refresh_table()
        self.win.close_bnt.clicked.connect(self.refresh)

    def del_record(self, id):
        # sql = 'update person set is_deleted = 1 where uuid = "{}"'.format(id)
        sql = 'delete from person where uuid = "{}"'.format(id)
        self.cur.execute(sql)
        self.db.con.commit()

    def regret_record(self, id):
        sql = 'update person set is_deleted = 0 where uuid = "{}"'.format(id)
        self.cur.execute(sql)
        self.db.con.commit()

    def refresh(self):
        self.query_record()

    def center(self):
        self.screen = QDesktopWidget().screenGeometry()
        self.size = self.geometry()
        self.move(int((self.screen.width()-self.size.width())/2),
                 int((self.screen.height()-self.size.height())/2)
                  )
    def to_pydatetime(self, a):
        b = datetime.datetime(1970, 1, 1, 0, 0) + datetime.timedelta(seconds=a)
        return QDateTime(b.year, b.month, b.day, b.hour, b.minute)


    def gen_graph(self):

        # self.db = DB()
        # self.cur = self.db.cur
        nodes = []
        links = []
        nodes2 = []
        links2 = []

        begin_date_interge = self.change_date(self.begin_date)
        end_date_interge = self.change_date(self.end_date)
        status_list = ['初筛阳性','无症状感染者','确诊病例','康复','疑似病例','临床诊断病例']

        if self.radio1.isChecked():
            # sql = '''
                # select person.name,person.serial,person.present_status
                # from person where (regist_date between {} and {}
                # '''.format(begin_date_interge,end_date_interge)
            sql = '''
                select person.name,person.serial,person.present_status
                from person  where (regist_date between {} and {} and present_status in {}
            '''.format(begin_date_interge,end_date_interge,tuple(status_list))
            sql2 = '''
                select person.name as namea,person.serial,b.name as nameb,b.serial,person.uuid,b.uuid
                from person,person as b,relation as c where
                person.uuid = c.uuid and b.uuid = c.contact_uuid and person.uuid in (
                select person.uuid from person where regist_date between {} and {}
                '''.format(begin_date_interge,end_date_interge)
            sql3 = ''' select location.address,person.name,person.serial from location,
                person where person.uuid = location.uuid and person.uuid in (
                select person.uuid from person where  regist_date between {} and {}
                '''.format(begin_date_interge,end_date_interge)
        else:
            sql = '''
                select person.name,person.serial,person.present_status
                from person  where (regist_date between {} and {} and present_status in {}
            '''.format(begin_date_interge,end_date_interge,tuple(status_list))
            sql2 = '''
                select person.name as namea,person.serial,b.name as nameb,b.serial,person.uuid,b.uuid
                from person,person as b,relation as c where (
                person.uuid = c.uuid and b.uuid = c.contact_uuid and person.present_status in {} and
                b.present_status in {} and person.regist_date between {}
                and {}
            '''.format(tuple(status_list),tuple(status_list),begin_date_interge,end_date_interge)
            sql3 = ''' select location.address,person.name,person.serial from location,
                person where person.uuid = location.uuid and person.uuid in (
                select person.uuid from person where person.regist_date between {} and {}
                and person.present_status in {}
            '''.format(begin_date_interge,end_date_interge,tuple(status_list))

        if self.name.text() == '':
            name_sql = ' ) '
        else:
            name_text_tmp = self.name.text()
            name_text = '%' + name_text_tmp + '%'
            name_sql = ''' and  (person.name like "%s" or
                        person.id like "%s")) '''%(name_text,name_text)

        # sql = '''select person.name,person.serial,cases.present_status from person
        #     left join cases on person.uuid = cases.uuid'''
        sql += name_sql
        self.cur.execute(sql)
        cases_rslt = self.cur.fetchall()
        cases_dic = {}
        color_list = ["#00FF00","#4682b4","#d3d7d4","#ff8c00",'#ff8c00',"#FF0000","#800000","#008800","#4b0082"]
        status_list = ['健康人群','一般接触者','密接','初筛阳性','疑似病例','无症状感染者','确诊病例','康复','死亡']
        for i in cases_rslt:
            try:
                cases_dic[i[0]+str(i[1])] = color_list[status_list.index(i[2])]
            except:
                cases_dic[i[0]+str(i[1])] = color_list[0]

        name_list = []
        for i in cases_rslt:
        #     nodes.append({'name':i[0]+str(i[1]),'symbolSize':10,'text':'abc'})
            if i[1]:
                serial = str(i[1])
            else:
                serial = ''
            name_list.append(i[0]+serial)

        for i in name_list:
            symbolsize = name_list.count(i) + 10
            # if symbolsize > 10:
                # symbolsize = 10
            try:
                color = cases_dic[i]
            except:
                color = Qt.red
            nodes.append({'name':i,'symbolSize':symbolsize,'itemStyle':{"color":color}})

        sql2 += name_sql
        self.cur.execute(sql2)
        relation = self.cur.fetchall()
        count = len(relation)
        name_dic = {}
        for i in range(count):
            if relation[i][1]:
                serial = str(relation[i][1])
            else:
                serial = ''
            mijie = relation[i][0] + serial
            yangxing = relation[i][2] + str(relation[i][3])
            try:
                if name_dic[mijie]:
                    tmp = name_dic[mijie]
                    tmp.append(yangxing)
                    name_dic[mijie] = tmp
            except:
                name_dic[mijie] = [yangxing,]

        mijie_list = []
        for key,value in name_dic.items():
            if key in name_list:
                for k in value:
                    links.append({"source":k,'target':key})
            else:
                # 显示共同密接
                # if len(value) > 1:
                if  True:
                    mijie_list.append(key)
                    for k in value:
                        links.append({"source":k,'target':key})

        # links.append({"source":relation[i][2]+str(relation[i][3]),'target':relation[i][0]+str(relation[i][1])})

        #
        nodes_loc = []
        sql3 += name_sql
        if self.loc_checkbox.isChecked():
            travel_dic = {}
            self.cur.execute(sql3)
            travel_rslt_tmp = self.cur.fetchall()
            for i in travel_rslt_tmp:
                name = i[1]+str(i[2])
                loc = i[0]
                try:
                    if travel_dic[loc]:
                        tmp = travel_dic[loc]
                        tmp.append(name)
                        travel_dic[loc] = tmp
                except:
                    travel_dic[loc] = [name,]

            print(travel_dic)
            for key,value in travel_dic.items():
                if len(value) > 1:
                    for name in value:
                        nodes_loc.append(key)
                        tmp3 = {'source':name,'target':key}
                        # tmp3 = {'source':i[0],'target':i[1]+str(i[2])}
                        if tmp3 not in links:
                            links.append(tmp3)

        print(nodes_loc,links)
        nodes_loc_set = set(nodes_loc)
        for i in nodes_loc_set:
            symbolsize = nodes_loc.count(i) + 5
            # if symbolsize > 10:
            #     symbolsize = 10

            nodes.append({'name':i,'symbolSize':symbolsize,'itemStyle':{"color":"#00ff00"}})

        # set_name_list = set(name_list)
        # print(set_name_list,len(set_name_list))
        for i in mijie_list:
            symbolsize = mijie_list.count(i) + 10
            # if symbolsize > 10:
                # symbolsize = 10
            # try:
                # color = cases_dic[i]
            # except:
                # color = Qt.black
            nodes.append({'name':i,'symbolSize':symbolsize,'itemStyle':{"color":"#d3d7d4"}})


        graph = Graph(init_opts = opts.InitOpts(width='1880px',height='1440px'))
        # graph.set_global_opts(toolbox_opts=opts.ToolboxOpts(is_show=True))
        graph.add(
            '',
            nodes+nodes2,
            links+links2,
            categories  = None,
            is_selected  = True,
            is_focusnode= True,
            is_roam= True,
            is_draggable= True,
            is_rotate_label= False,
            layout= "force",
            repulsion = 300,
            edge_length = 100,
            symbol = None,
            symbol_size= 50,
            gravity= 0.2,
            edge_label= None,
            edge_symbol = None,
            linestyle_opts=opts.LineStyleOpts(color='target',curve= 0)
            )
        try:
            file,ok =  QFileDialog. getSaveFileName(self,'文件保存','{}'.format('关系图.html'),'Html Files (*.html)')
            page = graph.render(file)
        except:
            pass

    def export_mijie(self,label):
        sql = ''' SELECT person1.name AS patient_name,person1.id AS id2,
            	person2.name,relation.relationship,person2.id,
                person2.gender,person2.age,person2.occup,person2.tel,person2.address,
                relation.contact_place,relation.contact_date,relation.contact_level,
                relation.contact_freq,person2.present_measure
            FROM
            	relation,
            	person AS person1,
            	person AS person2
            WHERE
            	relation.contact_uuid = person1.uuid
            	AND relation.uuid = person2.uuid
            	AND relation.contact_uuid IN ( SELECT uuid FROM person WHERE person.present_status
            '''
        if label:
            sql += '''
                in ( '初筛阳性', '无症状感染者', '疑似病例', '确诊病例', '康复' ) );
            '''
            name = '密接'
        else:
            sql += ''' = '密接') '''
            name = '次密接'

        self.cur.execute(sql)
        rslt = self.cur.fetchall()
        print(rslt)
        if rslt:
            self.export(rslt,name)

    def export_patient(self):
        sql = '''select serial,name,id,gender,age,address,occup,company,
            regist_date,present_status,present_measure,backup
            from person where present_status
        in ( '初筛阳性', '无症状感染者', '疑似病例', '确诊病例', '康复' ) ;
        '''
        self.cur.execute(sql)
        rslt = self.cur.fetchall()
        if rslt:
            print(rslt)
            self.export(rslt,'病例')


    def keyPressEvent(self, e):
        if e.key() == Qt.Key_Return:
            self.query_record()


    def export(self,rslt,label='tmp'):
        if rslt:
            workbook = xlwt.Workbook(encoding='utf-8')
            sheet = workbook.add_sheet("Sheet1")
            if label in ['密接','次密接']:
                head = ['序号', '重点人群','病例姓名','病例身份证号码','密接姓名',
                         '关系','身份证号','性别','年龄','职业','联系电话','现住址',
                        '接触地点','末次接触时间','接触方式','接触频率','防护情况',
                        '是否外地','是否需要协查','管控措施','解除隔离日期','备注']
            else:
                head = ['病例编号','姓名','身份证号码','性别','年龄','住址','职业',
                        '工作单位','发病日期','病例类型','状态','备注']
            cols = len(head)
            rows = len(rslt)
            for col in range(cols):
                sheet.write(0,col,head[col])
            for i in range(rows):
                rslti = rslt[i]
                if label in ['密接','次密接']:
                    tmp = ['','',]
                    tmp += [k for k in rslti][0:11]
                    tmp += [self.to_xls_date(rslti[11]),]
                    tmp += [rslti[12],rslti[13],'','','',rslti[14],'','']
                    print(tmp)
                else:
                    tmp = [k for k in rslti]
                    tmp[8] = self.to_xls_date(tmp[8])
                for col in range(cols):
                    sheet.write(i+1,col,tmp[col])
            filename,ok = QFileDialog.getSaveFileName(self,'保存结果文件','{}一览表.xls'.format(label),'Excel Files (*.xls)')
            if filename:
                workbook.save(filename)
                self.messagebox_auto_close("保存成功")

    def to_xls_date(self,t):
        a = datetime.datetime(1970,1,1) + datetime.timedelta(seconds=t)
        b = a.strftime('%Y-%m-%d')
        return b

    def messagebox_auto_close(self,txt = '完成!!!',time =1):
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Information)
        msg.setWindowTitle("# WARNING: ")
        msg.setText(txt)
        msg.setStandardButtons(QMessageBox.Yes)
        msg.button(QMessageBox.Yes).animateClick(time*1000)
        msg.exec_()
